﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace WebFormBlog
{
    public partial class Layout : System.Web.UI.MasterPage
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            string site_title = WebConfigurationManager.AppSettings["SiteTitle"].ToString();
            string feed = WebConfigurationManager.AppSettings["feed"].ToString();
            string site_url = WebConfigurationManager.AppSettings["SiteUrl"].ToString();
            string site_desc = WebConfigurationManager.AppSettings["SiteDescription"].ToString();
            string twitter = WebConfigurationManager.AppSettings["Twitter"].ToString();
            string github = WebConfigurationManager.AppSettings["Github"].ToString();
            if (string.IsNullOrEmpty(site_url))
                site_url = "/";

            FeedLink.Attributes["href"] = feed;
            FeedLink.Attributes["title"] = site_title;
            NavTitleAnchor.Attributes["href"] = site_url;
            NavTitleAnchor.InnerText = site_title;
            SiteDescription.Text = site_desc;

            if (string.IsNullOrEmpty(twitter))
                TwitterAnchor.Visible = false;
            else
                TwitterAnchor.Attributes["href"] = "https://twitter.com/" + twitter;

            if (string.IsNullOrEmpty(github))
                GithubAnchor.Visible = false;
            else
                GithubAnchor.Attributes["href"] = "http://github.com/" + github;

            /* 数据集查询方式
            BlogDataSetTableAdapters.categoryTableAdapter cta = new BlogDataSetTableAdapters.categoryTableAdapter();
            LayoutCategoriesRepeater.DataSource = cta.GetData();
            LayoutCategoriesRepeater.DataBind();
            */

            BlogModel.BlogEntities db = new BlogModel.BlogEntities();
            // 实体模型查询方式
            var categories = from category in db.categories select category;
            LayoutCategoriesRepeater.DataSource = categories;
            LayoutCategoriesRepeater.DataBind();

            /* 数据集查询方式
            BlogDataSetTableAdapters.linkTableAdapter lta = new BlogDataSetTableAdapters.linkTableAdapter();
            LayoutLinksRepeater.DataSource = lta.GetData();
            LayoutLinksRepeater.DataBind();
            */

            var links = from link in db.links select link;
            LayoutLinksRepeater.DataSource = links;
            LayoutLinksRepeater.DataBind();

            var posts = from post in db.posts
                        group post by post.created_at.Year into g
                        select new
                        {
                            year = g.Key,
                            posts = g.Count()
                        };
            //LayoutArchivesRepeater.DataSource = this.GetLayoutArchives();
            LayoutArchivesRepeater.DataSource = posts;
            LayoutArchivesRepeater.DataBind();
        }

        protected SqlDataReader GetLayoutArchives()
        {
            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["blog"].ConnectionString);
            string sql = "SELECT CONVERT(varchar(4), created_at, 120) AS year, COUNT(id) AS posts FROM dbo.post GROUP BY CONVERT(varchar(4), created_at, 120)";
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // let SqlDataReader automatically close connection
            }
            catch (Exception ex)
            {

            }
            return reader;
        }
    }

}